package com.xo.xodemo.controller.resource;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.mongodb.client.MongoCollection;
import com.monitorjbl.xlsx.StreamingReader;
import com.monitorjbl.xlsx.impl.StreamingRow;
import com.xo.xodemo.common.MongoResult;
import com.xo.xodemo.common.Result;
import com.xo.xodemo.model.*;
import org.apache.ibatis.annotations.Param;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author Administrator
 */
@Controller
@RequestMapping("/resource")
public class ResourceController {
    @Autowired
    MongoTemplate mtb;
    //资源管理
    @RequestMapping ("/sync")
    @ResponseBody
    public Result resourcesync(@Param("file")MultipartFile file, Model model) throws IOException {
        mtb.dropCollection("excel");
        mtb.dropCollection("sheetName");
        List<SheetName> excelNameList=new ArrayList<>();
        //给数据的编号
        int count=1;
        InputStream in = file.getInputStream();
        Workbook wk = StreamingReader.builder()
                .rowCacheSize(100)  //缓存到内存中的行数，默认是10
                .bufferSize(4096)  //读取资源时，缓存到内存的字节大小，默认是1024
                .open(in);  //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
        for(int j=0;j<wk.getNumberOfSheets();j++){
            Sheet sheet = wk.getSheetAt(j);
            List<Excel> excelList=new ArrayList<>();
            SheetName en=new SheetName();
            en.setName(sheet.getSheetName());
            excelNameList.add(en);
            boolean isTitle = true; // 用于跳过标题
            for (Row row : sheet) {
                Excel excel=new Excel();
                if (isTitle) {    // 跳过第一行标题，需要标题时可以注释掉判断
                    isTitle = false;
                    continue;
                }
                Map<Integer, String> map = new HashMap<>();
                List<String> list = new ArrayList<>(13);

                //遍历所有的列
                Set<Map.Entry<Integer, Cell>> entries = ((StreamingRow) row).getCellMap().entrySet();
                Iterator<Map.Entry<Integer, Cell>> iterator = entries.iterator();

                // 遍历得到键值对，存入新的map中
                while (iterator.hasNext()) {
                    Map.Entry<Integer, Cell> next = iterator.next();
                    Integer key = next.getKey();
                    String value = next.getValue().getStringCellValue();
                    map.put(key, value);    //这一步很重要啊，当表中存在空值时，是直接跳过的，所以要用key记录一下索引，后面遍历使用
                }
                // 遍历map，存入list
                for (int i = 0; i < 13; i++) {
                    String content = map.get(i);
                    // 避免出现空的情况，因为当表含有空数据时是直接跳过的，所以key存储索引就起到了作用
                    list.add(content);
                }
                if("".equals(list.get(2))){
                    continue;
                }
                excel.setNumberid(count);
                count++;
                excel.setName(list.get(1));
                excel.setTelephone(list.get(2));
                excel.setScreening_time(list.get(3));
                excel.setDivide_time(list.get(4));
                excel.setPromote_belong(list.get(5));
                excel.setBase_situation(list.get(6));
                excel.setImport_time(list.get(7));
                excel.setSale_belong(list.get(8));
                excel.setRemarks(list.get(9));
                excel.setResource_from(list.get(10));
                excel.setRefund_time(list.get(11));
                excel.setRefund_money(list.get(12));
                excel.setSheetName(sheet.getSheetName());
                excel.setEnter_time(new SimpleDateFormat("yyyyMMdd").format(new Date()));
                excelList.add(excel);
            }
            mtb.insertAll(excelList);
        }
        excelNameList = excelNameList.stream().distinct().collect(Collectors.toList());
        mtb.insertAll(excelNameList);
        return Result.success();
    }
    @RequestMapping ("/addnew")
    @ResponseBody
    public Result resourceaddnew(@Param("file")MultipartFile file, Model model) throws IOException {
        mtb.dropCollection("reExcel");
        mtb.dropCollection("notReExcel");
        List<SheetName> excelNameList = mtb.find(new Query(),SheetName.class, "sheetName");
        Excel endexcel=mtb.findOne(new Query().with(Sort.by(Sort.Direction.DESC, "numberid")),Excel.class);
        Integer repeatCount=0;
        Integer endnumber=endexcel.getNumberid();
        InputStream in = file.getInputStream();
        Workbook wk = StreamingReader.builder()
                .rowCacheSize(100)
                .bufferSize(4096)
                .open(in);
        for(int j=0;j<wk.getNumberOfSheets();j++){
            Sheet sheet = wk.getSheetAt(j);
            SheetName en=new SheetName();
            en.setName(sheet.getSheetName());
            excelNameList.add(en);
            List<Excel> excelList=new ArrayList<>();
            List<Excel> reexcelList=new ArrayList<>();
            List<Excel> notreexcelList=new ArrayList<>();
            boolean isTitle = true;
            for (Row row : sheet) {
                Excel excel=new Excel();
                if (isTitle) {
                    isTitle = false;
                    continue;
                }
                Map<Integer, String> map = new HashMap<>();
                List<String> list = new ArrayList<>(13);
                //遍历所有的列
                Set<Map.Entry<Integer, Cell>> entries = ((StreamingRow) row).getCellMap().entrySet();
                Iterator<Map.Entry<Integer, Cell>> iterator = entries.iterator();

                // 遍历得到键值对，存入新的map中
                while (iterator.hasNext()) {
                    Map.Entry<Integer, Cell> next = iterator.next();
                    Integer key = next.getKey();
                    String value = next.getValue().getStringCellValue();
                    map.put(key, value);
                }
                // 遍历map，存入list
                for (int i = 0; i < 13; i++) {
                    String content = map.get(i);
                    list.add(content);
                }
                if("".equals(list.get(2))){
                    continue;
                }
                Query query=new Query();
                if (!StringUtils.isEmpty(list.get(2))) {
                        query.addCriteria(Criteria.where("telephone").is(list.get(2)));

                }
               List<Excel> le=mtb.find(query,Excel.class);
                if(le.size()<=0){
                    excel.setNumberid(++endnumber);
                    excel.setName(list.get(1));
                    excel.setTelephone(list.get(2));
                    excel.setScreening_time(list.get(3));
                    excel.setDivide_time(list.get(4));
                    excel.setPromote_belong(list.get(5));
                    excel.setBase_situation(list.get(6));
                    excel.setImport_time(list.get(7));
                    excel.setSale_belong(list.get(8));
                    excel.setRemarks(list.get(9));
                    excel.setResource_from(list.get(10));
                    excel.setRefund_time(list.get(11));
                    excel.setRefund_money(list.get(12));
                    excel.setEnter_time(new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()));
                    NotReExcel notReExcel=new NotReExcel();

                    BeanUtils.copyProperties(excel,notReExcel);
                    excelList.add(excel);
                    notreexcelList.add(notReExcel);
                }
                else {
                    ReExcel reExcel=new ReExcel();
                    reExcel.setNumberid(le.get(0).getNumberid());
                    reExcel.setName(list.get(1));
                    reExcel.setTelephone(list.get(2));
                    reExcel.setScreening_time(list.get(3));
                    reExcel.setDivide_time(list.get(4));
                    reExcel.setPromote_belong(list.get(5));
                    reExcel.setBase_situation(list.get(6));
                    reExcel.setImport_time(list.get(7));
                    reExcel.setSale_belong(list.get(8));
                    reExcel.setRemarks(list.get(9));
                    reExcel.setResource_from(list.get(10));
                    reExcel.setRefund_time(list.get(11));
                    reExcel.setRefund_money(list.get(12));
                    reExcel.setEnter_time(new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()));
                    reexcelList.add(reExcel);
                    repeatCount++;

                }

            }
            mtb.insertAll(excelList);
            mtb.insertAll(reexcelList);
            mtb.insertAll(notreexcelList);
        }
        excelNameList = excelNameList.stream().distinct().collect(Collectors.toList());
        mtb.dropCollection("sheetName");
        mtb.insertAll(excelNameList);
        return Result.success();
    }
    @RequestMapping ("/getreturnexcel")
    public Result getreturnexcel(HttpServletResponse response) throws IOException {
        Query query=new Query();
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        List<ReExcel> result =new ArrayList<>();
        List<NotReExcel> result1 =new ArrayList<>();
        Sheet sh = wb.createSheet("重复数据");
        Row titlerow = sh.createRow(0);
        titlerow.createCell(0).setCellValue("序号");
        titlerow.createCell(1).setCellValue("客户姓名");
        titlerow.createCell(2).setCellValue("手机号码");
        titlerow.createCell(3).setCellValue("筛选时间");
        titlerow.createCell(4).setCellValue("分配时间推广部");
        titlerow.createCell(5).setCellValue("推广部归属");
        titlerow.createCell(6).setCellValue("客户基本情况");
        titlerow.createCell(7).setCellValue("输送时间");
        titlerow.createCell(8).setCellValue("销售部归属");
        titlerow.createCell(9).setCellValue("备注");
        titlerow. createCell(10).setCellValue("来源");
        titlerow.createCell(11).setCellValue("退款时间");
        titlerow.createCell(12).setCellValue("退款金额");
        result = mtb.find(query, ReExcel.class, "reExcel");
        for(int rownum = 0; rownum < result.size(); rownum++){
            Row row = sh.createRow((rownum+1));
            Excel excel= result.get(rownum);
            row.createCell(0).setCellValue(excel.getNumberid());
            row.createCell(1).setCellValue(excel.getName());
            row.createCell(2).setCellValue(excel.getTelephone());
            row.createCell(3).setCellValue(excel.getScreening_time());
            row.createCell(4).setCellValue(excel.getDivide_time());
            row.createCell(5).setCellValue(excel.getPromote_belong());
            row.createCell(6).setCellValue(excel.getBase_situation());
            row.createCell(7).setCellValue(excel.getImport_time());
            row.createCell(8).setCellValue(excel.getSale_belong());
            row.createCell(9).setCellValue(excel.getRemarks());
            row.createCell(10).setCellValue(excel.getResource_from());
            row.createCell(11).setCellValue(excel.getRefund_time());
            row.createCell(12).setCellValue(excel.getRefund_money());
        }
        Sheet sh1 = wb.createSheet("不重复数据");
        Row titlerow1 = sh1.createRow(0);
        titlerow1.createCell(0).setCellValue("序号");
        titlerow1.createCell(1).setCellValue("客户姓名");
        titlerow1.createCell(2).setCellValue("手机号码");
        titlerow1.createCell(3).setCellValue("筛选时间");
        titlerow1.createCell(4).setCellValue("分配时间推广部");
        titlerow1.createCell(5).setCellValue("推广部归属");
        titlerow1.createCell(6).setCellValue("客户基本情况");
        titlerow1.createCell(7).setCellValue("输送时间");
        titlerow1.createCell(8).setCellValue("销售部归属");
        titlerow1.createCell(9).setCellValue("备注");
        titlerow1.createCell(10).setCellValue("来源");
        titlerow1.createCell(11).setCellValue("退款时间");
        titlerow1.createCell(12).setCellValue("退款金额");
        result1 = mtb.find(query, NotReExcel.class, "notReExcel");
        for(int rownum = 0; rownum < result1.size(); rownum++){
            Row row = sh1.createRow((rownum+1));
            Excel excel= result1.get(rownum);
            row.createCell(0).setCellValue(excel.getNumberid());
            row.createCell(1).setCellValue(excel.getName());
            row.createCell(2).setCellValue(excel.getTelephone());
            row.createCell(3).setCellValue(excel.getScreening_time());
            row.createCell(4).setCellValue(excel.getDivide_time());
            row.createCell(5).setCellValue(excel.getPromote_belong());
            row.createCell(6).setCellValue(excel.getBase_situation());
            row.createCell(7).setCellValue(excel.getImport_time());
            row.createCell(8).setCellValue(excel.getSale_belong());
            row.createCell(9).setCellValue(excel.getRemarks());
            row.createCell(10).setCellValue(excel.getResource_from());
            row.createCell(11).setCellValue(excel.getRefund_time());
            row.createCell(12).setCellValue(excel.getRefund_money());
        }
        //直接写文件名编码不对
        String downloadFileName= new String("输出文件".getBytes(),"ISO-8859-1");
        response.setContentType( "application/x-msdownload");
        response.setHeader("Content-Disposition", "attachment;filename="+downloadFileName+".xls");
        ServletOutputStream outputStream = null;
        outputStream = response.getOutputStream();
        wb.write(outputStream);
        outputStream.flush();
        return null;
    }
    @RequestMapping ("/query")
    @ResponseBody
    public MongoResult query(@Param("page") Integer page, @Param("limit")Integer limit,@Param("telephone") String telephone,@Param("enter_time") String enter_time,@Param("name") String name,@Param("screening_time") String screening_time,@Param("divide_time") String divide_time,@Param("base_situation") String base_situation,@Param("promote_belong") String
            promote_belong,@Param("sale_belong") String sale_belong,@Param("remarks") String remarks,@Param("trade_date") String trade_date,@Param("money") String money,@Param("customer_service") String  customer_service
            , @Param("resource_from")String resource_from, @Param("sheetName")String sheetName) throws IOException {
        Query query=new Query();
        if (!StringUtils.isEmpty(telephone)) {

                query.addCriteria(Criteria.where("telephone").regex(telephone));
            }

        if (!StringUtils.isEmpty(enter_time)) {

                query.addCriteria(Criteria.where("enter_time").regex(enter_time));
            }
        if (!StringUtils.isEmpty(name)) {
                query.addCriteria(Criteria.where("name").regex(name));
            }
        if (!StringUtils.isEmpty(screening_time)) {
                query.addCriteria(Criteria.where("screening_time").regex(screening_time));
            }
        if (!StringUtils.isEmpty(divide_time)) {
                query.addCriteria(Criteria.where("divide_time").regex(divide_time));
            }
        if (!StringUtils.isEmpty(base_situation)) {
                query.addCriteria(Criteria.where("base_situation").regex(base_situation));
            }

        if (!StringUtils.isEmpty(promote_belong)) {

                query.addCriteria(Criteria.where("promote_belong").regex(promote_belong));
            }
        if (!StringUtils.isEmpty(sale_belong)) {

                query.addCriteria(Criteria.where("sale_belong").regex(sale_belong));
            }

        if (!StringUtils.isEmpty(remarks)) {
                query.addCriteria(Criteria.where("remarks").regex(remarks));
        }
        if (!StringUtils.isEmpty(trade_date)) {
        
                query.addCriteria(Criteria.where("trade_date").regex(trade_date));
            }
        if (!StringUtils.isEmpty(money)) {
                query.addCriteria(Criteria.where("money").regex(money));}
        if (!StringUtils.isEmpty(customer_service)) {

                query.addCriteria(Criteria.where("customer_service").regex(customer_service));
        }
        if (!StringUtils.isEmpty(resource_from)) {
                query.addCriteria(Criteria.where("resource_from").regex(resource_from));
        }
        if (!StringUtils.isEmpty(sheetName)) {
                query.addCriteria(Criteria.where("sheetName").regex(sheetName));
        }
        query.with(Sort.by(Sort.Direction.DESC, "numberid"));
        Pageable pageable = PageRequest.of(page-1,limit);
        long count=mtb.count(query,"excel");
        query.with(pageable);
        List<Excel> le=  mtb.find(query,Excel.class);
        MongoResult result =MongoResult.success(le);
         result.setCount(count);
        return  result;


    }
    @RequestMapping ("/update")
    @ResponseBody
    public Result update(@RequestParam("idedit") String id, @RequestParam("telephoneedit") String telephone, @RequestParam("enter_timeedit") String enter_time, @RequestParam("nameedit") String name, @RequestParam("screening_timeedit") String screening_time, @RequestParam("divide_timeedit") String divide_time, @RequestParam("base_situationedit") String base_situation, @RequestParam("promote_belongedit") String
            promote_belong, @RequestParam("sale_belongedit") String sale_belong, @RequestParam("remarksedit") String remarks, @RequestParam("trade_dateedit") String trade_date, @RequestParam("moneyedit") String money, @RequestParam("customer_serviceedit") String  customer_service
            , @RequestParam("resource_fromedit")String resource_from, @RequestParam("refund_timeedit")String refund_time, @RequestParam("refund_moneyedit")String refund_money ) throws IOException {
        Query query=new Query();
        query.addCriteria(Criteria.where("_id").is(id));
        Update update = new Update();
        update.set("telephone",telephone);
        update.set("enter_time",enter_time);
        update.set("name",name);
        update.set("screening_time",screening_time);
        update.set("divide_time",divide_time);
        update.set("base_situation",base_situation);
        update.set("promote_belong", promote_belong);
        update.set("sale_belong",sale_belong);
        update.set("remarks",remarks);
        update.set("trade_date",trade_date);
        update.set("money",money);
        update.set("customer_service",customer_service);
        update.set("resource_from",resource_from);
        update.set("refund_time",refund_time);
        update.set("refund_money",refund_money);
//        excel.setTelephone(telephone);
//        excel.setEnter_time(enter_time);
//        excel.setName(name);
//        excel.setScreening_time(screening_time);
//        excel.setDivide_time(divide_time);
//        excel.setBase_situation(base_situation);
//        excel.setPromote_belong( promote_belong);
//        excel.setSale_belong(sale_belong);
//        excel.setRemarks(remarks);
//        excel.setTrade_date(trade_date);
//        excel.setMoney(money);
//        excel.setCustomer_service(customer_service);
//        excel.setResource_from(resource_from);
        mtb.updateFirst(query,update,Excel.class);
//        mtb.remove(query,Excel.class);
//        mtb.insert(excel);
        return Result.success();
    }
//    @RequestMapping ("/updatemany")
//    @ResponseBody
//    public Result updatemany(@RequestParam(value="selectlist")List<UpdateManyMiddle> selectlist, @RequestParam(value="updatelist")List<UpdateManyMiddle> updatelist) throws IOException {
//        Query query=new Query();
//        Update update=new Update();
//        for(UpdateManyMiddle umm:selectlist){
//            if(umm.getCharacter().equals("=")){
//              query.addCriteria(Criteria.where(umm.getBasefield()).is(umm.getFinalfield()));
//            }
//            else if(umm.getCharacter().equals("!=")){
//                query.addCriteria(Criteria.where(umm.getBasefield()).ne(umm.getFinalfield()));
//            }
//            else if(umm.getCharacter().equals("<")){
//                query.addCriteria(Criteria.where(umm.getBasefield()).lt(umm.getFinalfield()));
//            }
//            else if(umm.getCharacter().equals("<=")){
//                query.addCriteria(Criteria.where(umm.getBasefield()).lte(umm.getFinalfield()));
//            }
//            else if(umm.getCharacter().equals(">")){
//                query.addCriteria(Criteria.where(umm.getBasefield()).is(umm.getFinalfield()));
//            }
//            else if(umm.getCharacter().equals(">=")){
//                query.addCriteria(Criteria.where(umm.getBasefield()).is(umm.getFinalfield()));
//            }
//        }
//        for(UpdateManyMiddle umm:updatelist){
//             update.set(umm.getBasefield(),umm.getFinalfield());
//        }
//        mtb.updateMulti(query,update,"EXCEL");
//        return Result.success();
//    }
    @RequestMapping ("/delete")
    @ResponseBody
    public Result update(@Param("id") String id){
        Query query=new Query();
        query.addCriteria(Criteria.where("_id").is(id));
        mtb.remove(query,Excel.class);
        return Result.success();
    }
    //把查询到的结果生成为文件返回去
    @RequestMapping ("/get")
    public void get(@Param("qtname") String qtname, @Param("qttelephone") String qttelephone, @Param("qtenter_time") String qtenter_time,  @Param("qtscreening_time") String qtscreening_time, @Param("qtdivide_time") String qtdivide_time, @Param("base_situation") String qtbase_situation, @Param("promote_belong") String
            qtpromote_belong, @Param("qtsale_belong") String qtsale_belong, @Param("qtremarks") String qtremarks, @Param("qttrade_date") String qttrade_date, @Param("qtmoney") String qtmoney, @Param("qtcustomer_service") String  qtcustomer_service
            , @Param("qtresource_from")String qtresource_from,@Param("outname")String outname, HttpServletResponse response) throws IOException {
        Query query=new Query();
        if (!StringUtils.isEmpty(qtname)) {
                query.addCriteria(Criteria.where("name").regex(qtname));
        }
        if (!StringUtils.isEmpty(qttelephone)) {
                query.addCriteria(Criteria.where("telephone").regex(qttelephone));
        }
        if (!StringUtils.isEmpty(qtenter_time)) {
                query.addCriteria(Criteria.where("enter_time").regex(qtenter_time));
        }
        if (!StringUtils.isEmpty(qtscreening_time)) {
                query.addCriteria(Criteria.where("screening_time").regex(qtscreening_time));
        }
        if (!StringUtils.isEmpty(qtbase_situation)) {
                query.addCriteria(Criteria.where("base_situation").regex(qtbase_situation));
        }
        if (!StringUtils.isEmpty(qtpromote_belong)) {
                query.addCriteria(Criteria.where("promote_belong").regex(qtpromote_belong));
        }
        if (!StringUtils.isEmpty(qtsale_belong)) {
                query.addCriteria(Criteria.where("sale_belong").regex(qtsale_belong));
        }
        if (!StringUtils.isEmpty(qtremarks)) {
                query.addCriteria(Criteria.where("remarks").regex(qtremarks));
        }
        if (!StringUtils.isEmpty(qttrade_date)) {
                query.addCriteria(Criteria.where("trade_date").regex(qttrade_date));
        }
        if (!StringUtils.isEmpty(qtmoney)) {
                query.addCriteria(Criteria.where("money").regex(qtmoney));

        }
        if (!StringUtils.isEmpty(qtcustomer_service)) {
                query.addCriteria(Criteria.where("customer_service").regex(qtcustomer_service));

        }
        if (!StringUtils.isEmpty(qtdivide_time)) {
                query.addCriteria(Criteria.where("divide_time").regex(qtdivide_time));

        }
        if (!StringUtils.isEmpty(qtresource_from)) {
                query.addCriteria(Criteria.where("resource_from").regex(qtresource_from));
        }
       long outamount=mtb.count(query,Excel.class);
        //需要几个sheet
        long sheetnum=outamount/1000;
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        //如果需要一个以上
        if(sheetnum>0){
            for(long i=0;i<sheetnum;i++){
                Sheet sh = wb.createSheet("数据表"+i);
                Row titlerow = sh.createRow(0);
                titlerow.createCell(0).setCellValue("序号");
                titlerow.createCell(1).setCellValue("客户姓名");
                titlerow.createCell(2).setCellValue("手机号码");
                titlerow.createCell(3).setCellValue("筛选时间");
                titlerow.createCell(4).setCellValue("分配时间推广部");
                titlerow.createCell(5).setCellValue("基本情况");
                titlerow.createCell(6).setCellValue("推广部归属");
                titlerow.createCell(7).setCellValue("输送时间");
                titlerow.createCell(8).setCellValue("销售归属");
                titlerow.createCell(9).setCellValue("备注");
                titlerow.createCell(10).setCellValue("交易时间");
                titlerow.createCell(11).setCellValue("金额");
                titlerow.createCell(12).setCellValue("成交客服");
                titlerow.createCell(13).setCellValue("退款时间");
                titlerow.createCell(14).setCellValue("退款金额");
                titlerow.createCell(15).setCellValue("入库时间");
                titlerow.createCell(16).setCellValue("资源来源");
                List<Excel> le=mtb.find(query.limit(1000).skip((i)*1000),Excel.class);
                for(int rownum = 1; rownum <= le.size(); rownum++){
                    Row row = sh.createRow(rownum);
                    Excel excel= le.get(rownum-1);
                    row.createCell(0).setCellValue(excel.getNumberid());
                    row.createCell(1).setCellValue(excel.getName());
                    row.createCell(2).setCellValue(excel.getTelephone());
                    row.createCell(3).setCellValue(excel.getScreening_time());
                    row.createCell(4).setCellValue(excel.getDivide_time());
                    row.createCell(5).setCellValue(excel.getBase_situation());
                    row.createCell(6).setCellValue(excel.getPromote_belong());
                    row.createCell(7).setCellValue(excel.getImport_time());
                    row.createCell(8).setCellValue(excel.getSale_belong());
                    row.createCell(9).setCellValue(excel.getRemarks());
                    row.createCell(10).setCellValue(excel.getTrade_date());
                    row.createCell(11).setCellValue(excel.getMoney());
                    row.createCell(12).setCellValue(excel.getCustomer_service());
                    row.createCell(13).setCellValue(excel.getRefund_time());
                    row.createCell(14).setCellValue(excel.getRefund_time());
                    row.createCell(15).setCellValue(excel.getEnter_time());
                    row.createCell(16).setCellValue(excel.getResource_from());
                }
            }
            Sheet sh = wb.createSheet("数据表"+sheetnum);
            Row titlerow = sh.createRow(0);
            titlerow.createCell(0).setCellValue("序号");
            titlerow.createCell(1).setCellValue("客户姓名");
            titlerow.createCell(2).setCellValue("手机号码");
            titlerow.createCell(3).setCellValue("筛选时间");
            titlerow.createCell(4).setCellValue("分配时间推广部");
            titlerow.createCell(5).setCellValue("基本情况");
            titlerow.createCell(6).setCellValue("推广部归属");
            titlerow.createCell(7).setCellValue("输送时间");
            titlerow.createCell(8).setCellValue("销售归属");
            titlerow.createCell(9).setCellValue("备注");
            titlerow.createCell(10).setCellValue("交易时间");
            titlerow.createCell(11).setCellValue("金额");
            titlerow.createCell(12).setCellValue("成交客服");
            titlerow.createCell(13).setCellValue("退款时间");
            titlerow.createCell(14).setCellValue("退款金额");
            titlerow.createCell(15).setCellValue("入库时间");
            titlerow.createCell(16).setCellValue("资源来源");
            int n= Math.toIntExact(outamount % 1000);

            List<Excel> le=mtb.find(query.limit(n).skip(sheetnum*1000),Excel.class);
            for(int rownum = 1; rownum <= le.size(); rownum++){
                Row row = sh.createRow(rownum);
                Excel excel= le.get(rownum-1);
                row.createCell(0).setCellValue(excel.getNumberid());
                row.createCell(1).setCellValue(excel.getName());
                row.createCell(2).setCellValue(excel.getTelephone());
                row.createCell(3).setCellValue(excel.getScreening_time());
                row.createCell(4).setCellValue(excel.getDivide_time());
                row.createCell(5).setCellValue(excel.getBase_situation());
                row.createCell(6).setCellValue(excel.getPromote_belong());
                row.createCell(7).setCellValue(excel.getImport_time());
                row.createCell(8).setCellValue(excel.getSale_belong());
                row.createCell(9).setCellValue(excel.getRemarks());
                row.createCell(10).setCellValue(excel.getTrade_date());
                row.createCell(11).setCellValue(excel.getMoney());
                row.createCell(12).setCellValue(excel.getCustomer_service());
                row.createCell(13).setCellValue(excel.getRefund_time());
                row.createCell(14).setCellValue(excel.getRefund_time());
                row.createCell(15).setCellValue(excel.getEnter_time());
                row.createCell(16).setCellValue(excel.getResource_from());
            }
        }
        //如只有一个
        else{
            Sheet sh = wb.createSheet("数据表0");
            Row titlerow = sh.createRow(0);
            titlerow.createCell(0).setCellValue("序号");
            titlerow.createCell(1).setCellValue("客户姓名");
            titlerow.createCell(2).setCellValue("手机号码");
            titlerow.createCell(3).setCellValue("筛选时间");
            titlerow.createCell(4).setCellValue("分配时间推广部");
            titlerow.createCell(5).setCellValue("推广部归属");
            titlerow.createCell(6).setCellValue("客户基本情况");
            titlerow.createCell(7).setCellValue("输送时间");
            titlerow.createCell(8).setCellValue("销售部归属");
            titlerow.createCell(9).setCellValue("备注");
            titlerow.createCell(10).setCellValue("来源");
            titlerow.createCell(11).setCellValue("退款时间");
            titlerow.createCell(12).setCellValue("退款金额");
            List<Excel> le=mtb.find(query.limit((int) (outamount%1000)).skip(sheetnum*1000),Excel.class);
            for(int rownum = 0; rownum < le.size(); rownum++){
                Row row = sh.createRow((int) (rownum+1+sheetnum*1000));
                Excel excel= le.get(rownum);
                row.createCell(0).setCellValue(excel.getNumberid());
                row.createCell(1).setCellValue(excel.getName());
                row.createCell(2).setCellValue(excel.getTelephone());
                row.createCell(3).setCellValue(excel.getScreening_time());
                row.createCell(4).setCellValue(excel.getDivide_time());
                row.createCell(5).setCellValue(excel.getBase_situation());
                row.createCell(6).setCellValue(excel.getPromote_belong());
                row.createCell(7).setCellValue(excel.getImport_time());
                row.createCell(8).setCellValue(excel.getSale_belong());
                row.createCell(9).setCellValue(excel.getRemarks());
                row.createCell(10).setCellValue(excel.getTrade_date());
                row.createCell(11).setCellValue(excel.getMoney());
                row.createCell(12).setCellValue(excel.getCustomer_service());
                row.createCell(13).setCellValue(excel.getRefund_time());
                row.createCell(14).setCellValue(excel.getRefund_time());
                row.createCell(15).setCellValue(excel.getEnter_time());
                row.createCell(16).setCellValue(excel.getResource_from());
            }
        }
        //直接写文件名编码不对
        String downloadFileName= new String(outname.getBytes(),"ISO-8859-1");
        response.setContentType( "application/x-msdownload");
        response.setHeader("Content-Disposition", "attachment;filename="+downloadFileName+".xls");
        ServletOutputStream outputStream = null;
        outputStream = response.getOutputStream();
        wb.write(outputStream);
        outputStream.flush();
    }
    @RequestMapping ("/updatemany")
    @ResponseBody
    public Result updatemany(@Param("selectdata")String selectdata, @Param("begin") Integer begin,@Param("end") Integer end) throws IOException {
        Query query=new Query();
        query.addCriteria(Criteria.where("numberid").gt(begin).lt(end));
        Update update=new Update();
        JSONObject jo=JSON.parseObject(selectdata);
        if(jo.getString("用户名")!=null&&!"".equals(jo.getString("用户名"))){
            update.set("name",jo.getString("用户名"));
        }

        if(jo.getString("电话号")!=null&&!"".equals(jo.getString("电话号"))){
            update.set("telephone",jo.getString("电话号"));
        }
        if(jo.getString("筛选时间")!=null&&!"".equals(jo.getString("筛选时间"))){
            update.set("screening_time",jo.getString("筛选时间")!=null);
        }
        if(jo.getString("分配时间")!=null&&!"".equals(jo.getString("分配时间"))){
            update.set("divide_time",jo.getString("分配时间"));
        }
        if(jo.getString("基本情况")!=null&&!"".equals(jo.getString("基本情况"))){
            update.set("base_situation",jo.getString("基本情况"));
        }
        if(jo.getString("推广归属")!=null&&!"".equals(jo.getString("推广归属"))){
            update.set("promote_belong",jo.getString("推广归属"));
        }
        if(jo.getString("输送时间")!=null&&!"".equals(jo.getString("输送时间"))){
            update.set("import_time",jo.getString("输送时间"));
        }
        if(jo.getString("销售归属")!=null&&!"".equals(jo.getString("销售归属"))){
            update.set("sale_belong",jo.getString("销售归属"));
        }
        if(jo.getString("备注")!=null&&!"".equals(jo.getString("备注"))){
            update.set("remarks",jo.getString("备注"));
        }
        if(jo.getString("交易时间")!=null&&!"".equals(jo.getString("交易时间"))){
            update.set("trade_date",jo.getString("交易时间"));
        }
        if(jo.getString("金额")!=null&&!"".equals(jo.getString("金额"))){
            update.set("money",jo.getString("金额"));
        }
        if(jo.getString("成交客服")!=null&&!"".equals(jo.getString("成交客服"))){
            update.set("customer_service",jo.getString("成交客服"));
        }
        if(jo.getString("退款时间")!=null&&!"".equals(jo.getString("退款时间"))){
            update.set("refund_time",jo.getString("退款时间"));
        }
        if(jo.getString("入库时间")!=null&&!"".equals(jo.getString("入库时间"))){
            update.set("enter_time",jo.getString("入库时间"));
        }
        if(jo.getString("资源来源")!=null&&!"".equals(jo.getString("资源来源"))){
            update.set("resource_from",jo.getString("资源来源"));
        }
        mtb.updateMulti(query,update,Excel.class);
        return  Result.success();
    }
    @RequestMapping ("/getnamelist")
    @ResponseBody
    public Result getnamelist(){
        List<SheetName> len=mtb.find(new Query(),SheetName.class);
        return Result.success(len);
    }
}
